package edu.upenn.yas;

import java.util.LinkedList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.ResultSet;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Timestamp;
import java.util.Date;

/** Singleton engine used by servlets to answer queries */
public class QueryEngine {
    // Cached datasource resources created once at initialization
    private InitialContext ctx = null;
    private DataSource ds = null;

    // The singleton instance clients use to access the engine
    public static final QueryEngine instance = new QueryEngine();

    // Private constructor to create the singleton Engine
    private QueryEngine() {
        try {
            if (ctx == null) {  
                ctx = new InitialContext();
            }
            if (ds == null) {
                ds = (DataSource)ctx.lookup("java:comp/env/jdbc/DSTest");
            }
        } catch (Exception e) {
            throw new DBException(e);
        }
    }

    /** Returns a list of all the user names of all registered users */
    public List<String> getAllUsers() {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT u.username FROM User u");
            while (srs.next()) {
                res.add(srs.getString("username"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    /** Returns the password of the specified user or null if the user does
      * not exist. */
    public String getPassword(String username) {
        String res = null;
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT u.password FROM User u WHERE u.username = ?");
            stmt.setString(1, username);
            ResultSet srs = stmt.executeQuery();
            // If there is something in the result set, then it's the password
            if (srs.next()) {
                res = srs.getString("password");
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> getThreeRandomUsers() {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT U.username FROM User U ORDER BY RAND() LIMIT 3");
            while (srs.next()) {
                res.add(srs.getString("username"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> getTopThreePosts() {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT DISTINCT TOP 3 P.title, P.content " +
                                              "FROM Post P SORT BY P.numViews");
            while (srs.next()) {
                res.add(srs.getString("P.title"));
		    res.add(srs.getString("P.content"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> GetRating(int uid) {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT AVG(VOP.value) FROM VotesOnPost VOP WHERE VOP.uid = ?");
            stmt.setInt(1, uid);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(srs.getString("value"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> GetPost(Integer uid) {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT P.url, P.title, P.content, P.datetime FROM Post P WHERE P.uid = ?");
            stmt.setInt(1, uid);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(srs.getString("url"));
                res.add(srs.getString("title"));
                res.add(srs.getString("content"));
                res.add(srs.getString("datetime"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> GetAuthor(Integer uid) {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT PA.username FROM PostsAbout PA WHERE PA.uid = ?");
            stmt.setInt(1, uid);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(srs.getString("username"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> GetASDF(Integer uid) {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT * FROM " + 
                    "PostsAbout PA, Post P WHERE P.uid = PA.uid AND PA.uid = 1");
            //stmt.setInt(1, uid);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(srs.getString("username"));
                res.add(srs.getString("title"));
                res.add(srs.getString("datetime"));
                res.add(srs.getString("content"));
                res.add(srs.getString("url"));
                res.add(srs.getString("numViews"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<ASDF.CommentTriples> getComments(Integer uid) {
        List<ASDF.CommentTriples> res = new LinkedList<ASDF.CommentTriples>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT COP.username, COP.body, COP.level FROM " +
                    "CommentsOnPost COP WHERE COP.uid = ? SORT BY COP.datetime");
            stmt.setInt(1, uid);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(new ASDF.CommentTriples(srs.getString("level"),
                                              srs.getString("username"),
                                              srs.getString("body")));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> GetUserInfo(String username) {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT * " + 
                    "FROM User u WHERE u.username = ?");
            stmt.setString(1, username);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(srs.getString("username"));
                res.add(srs.getString("firstName"));
                res.add(srs.getString("lastName"));
                res.add(srs.getString("location"));
                res.add(srs.getString("profession"));
                res.add(srs.getString("age"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<User.UIDTitlePair> getUserPosts(String username) {
        List<User.UIDTitlePair> res = new LinkedList<User.UIDTitlePair>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT P.uid, P.title FROM User U, PostsAbout PA, Post P " +
                    "WHERE U.username = PA.username AND PA.uid = P.uid AND U.username = ?");
            stmt.setString(1, username);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                res.add(new User.UIDTitlePair(srs.getString("uid"),
                                              srs.getString("title")));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    public List<String> getFriends(String username) {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT *" +
                    "FROM FriendsWith f " +
                    "WHERE f.lhFriend = ? OR f.rhFriend = ?");
            stmt.setString(1, username);
            stmt.setString(2, username);
            ResultSet srs = stmt.executeQuery();
            while (srs.next()) {
                String lhFriend = srs.getString("lhFriend");
                String rhFriend = srs.getString("rhFriend");
                if (lhFriend.equals(username)) {
                    res.add(rhFriend);
                } else {
                    res.add(lhFriend);
                }
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    /** Adds the given friend to the table, returning false if the
      * desired friend isn't registered; true otherwise */
    public boolean addFriend(String user, String friend) {
        if (!isUser(friend)) { return false; }
        Connection conn = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;
        try {
            conn = ds.getConnection();
            // Check to see if this is a duplicate
            stmt1 = conn.prepareStatement(
                "SELECT * FROM FriendsWith f " +
                "WHERE (f.lhFriend = ? AND f.rhFriend = ?) OR " +
                     " (f.lhFriend = ? AND f.rhFriend = ?)");
            stmt1.setString(1, user);
            stmt1.setString(2, friend);
            stmt1.setString(3, friend);
            stmt1.setString(4, user);
            ResultSet srs = stmt1.executeQuery();
            // If not, then add a new friends entry
            if (!srs.next()) {
                stmt2 = conn.prepareStatement(
                    "INSERT INTO FriendsWith (lhFriend, rhFriend) " +
                    " Values (?, ?)");
                stmt2.setString(1, user);
                stmt2.setString(2, friend);
                stmt2.executeUpdate();
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt1 != null) { stmt1.close(); }
                if (stmt2 != null) { stmt2.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return true;
    }


    /** Adds the given submission to the table, returning false if the
      * fields are missing or URL is a duplicate*/
    public boolean addASDF(String URL, String title, String content, String username) {
        if (URL.isEmpty()) { return false; }
        if (title.isEmpty()) { return false; }
        if (content != null) { content = ""; }
        Connection conn = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;
        PreparedStatement stmt3 = null;
        PreparedStatement stmt4 = null;
        try {
            conn = ds.getConnection();
            // Check to see if this is a duplicate
            stmt1 = conn.prepareStatement(
                "SELECT P.url FROM Post P " +
                "WHERE P.url = ?");
            stmt1.setString(1, URL);
            ResultSet srs = stmt1.executeQuery();
            // If not, then add a new friends entry
            if (!srs.next()) {
                stmt4 = conn.prepareStatement(
                    "SELECT MAX(p.uid) AS \"out\" FROM Post p");
                ResultSet srs4 = stmt4.executeQuery();
                srs4.next();
                int uid = srs4.getInt("out") + 1;

                stmt2 = conn.prepareStatement(
                    "INSERT INTO PostsAbout (uid, username) VALUES (?, ?)");
                stmt2.setInt(1, uid);
                stmt2.setString(2, username);
                stmt2.executeUpdate();


                stmt3 = conn.prepareStatement(
                    "INSERT INTO Post (uid, title, datetime, content, url, category, numViews) VALUES (?, ?, ?, ?, ?, ?, ?)");
                stmt3.setInt   (1, uid++);
                stmt3.setString(2, title);
                stmt3.setTimestamp(3, new Timestamp(new Date().getTime()));
                stmt3.setString(4, "");
                stmt3.setString(5, URL);
                stmt3.setString(6, "");
                stmt3.setInt(7, 0);
                stmt3.executeUpdate();
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt1 != null) { stmt1.close(); }
                if (stmt2 != null) { stmt2.close(); }
                if (stmt3 != null) { stmt3.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return true;
    }

    /** Returns true if the given username is registered in the db. */
    public boolean isUser(String username) {
        boolean res = false;
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                    "SELECT *" +
                    "FROM User u " +
                    "WHERE u.username = ?");
            stmt.setString(1, username);
            ResultSet srs = stmt.executeQuery();
            // If there is a result, it's our user
            if (srs.next()) {
                res = true;
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    /** Returns all stored URLs */
    public List<String> getAllURLs() {
        List<String> res = new LinkedList<String>();
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.createStatement();
            ResultSet srs = stmt.executeQuery("SELECT p.url FROM Post p");
            while (srs.next()) {
                res.add(srs.getString("url"));
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
        return res;
    }

    /** Removes the tuple with the given URL. */
    public void removeURL(String url) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ds.getConnection();
            stmt = conn.prepareStatement(
                "DELETE FROM Post " +
                "WHERE url = ?");
            stmt.setString(1, url);
            stmt.executeUpdate();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            try {
                if (stmt != null) { stmt.close(); }
                if (conn != null) { conn.close(); }
            } catch (Exception e) { throw new DBException(e); }
        }
    }
}
